package datamodule;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import util.FData;
import util.FString;
import conexao.Connect;

public class ChamadaDAO {


	private DBuserVO userVO;

	public ChamadaDAO( DBuserVO userVO){
		this.userVO = userVO;
	}
	//Inserir Chamada
	public int inserir(ChamadaVO VOcha) throws ClassNotFoundException, SQLException {
		Connection con = Connect.getConnection(userVO);
		PreparedStatement pstmtInsert = con.prepareStatement("INSERT INTO chamada(cha_Cod_Cha, alu_Cod_Alu, "+
				" cla_Cod_Cla, igr_Cod_Igreja, tur_Cod_Tur,  cha_DT_Cha, cha_PF)  " +
				" VALUES(?,?,?,?,?,?,?) ");
		pstmtInsert.setString(1, VOcha.getCha_Cod_Cha());			
		pstmtInsert.setString(2, VOcha.getAlu_Cod_Alu());
		pstmtInsert.setString(3, VOcha.getCla_Cod_Cla());
		pstmtInsert.setString(4, VOcha.getIgr_Cod_Igreja());
		pstmtInsert.setString(5, VOcha.getTur_Cod_Tur());
		pstmtInsert.setString(6, VOcha.getCha_DT_Cha_SQL());
		pstmtInsert.setString(7, VOcha.getCha_PF());
		System.out.println(pstmtInsert);
		int reg = pstmtInsert.executeUpdate();	
		con.close();
		return reg;
	}

	//Alterar Chamada
	public int alterar(ChamadaVO VOcha) throws ClassNotFoundException, SQLException {
		Connection con = Connect.getConnection(userVO);
		String virgula = "";
		StringBuffer sql = new StringBuffer(" UPDATE chamada SET ");

		if (!VOcha.getCha_Cod_Cha().equals(FString.ignorar)) {
			sql.append(virgula+" cha_Cod_Cha = '"+VOcha.getCha_Cod_Cha()+"' ");
			virgula = ",";
		}
		
		if (!VOcha.getAlu_Cod_Alu().equals(FString.ignorar)) {
			sql.append(virgula+" alu_Cod_Alu = '"+VOcha.getAlu_Cod_Alu()+"' ");
			virgula = ",";
		}
		
		if (!VOcha.getIgr_Cod_Igreja().equals(FString.ignorar)) {
			sql.append(virgula+" igr_Cod_Igreja = '"+VOcha.getIgr_Cod_Igreja()+"' ");
			virgula = ",";
		}
				
		if (!VOcha.getTur_Cod_Tur().equals(FString.ignorar)) {
			sql.append(virgula+" tur_Cod_Tur = '"+VOcha.getTur_Cod_Tur()+"' ");
			virgula = ",";
		}
	
		if (!VOcha.getCla_Cod_Cla().equals(FString.ignorar)) {
			sql.append(virgula+" cla_Cod_Cla = '"+VOcha.getCla_Cod_Cla()+"' ");
			virgula = ",";
		}
		
		if (VOcha.getCha_DT_Cha()  != FData.ignorarD) {
			sql.append(virgula+" cha_DT_Cha = '"+(VOcha.getCha_DT_Cha()== FData.ignorarD || VOcha.getCha_DT_Cha() == null? FData.dataDefaultSQL(): VOcha.getCha_DT_Cha_SQL())+"' ");
			virgula = ",";
		}
		
		if (VOcha.getCha_PF() != FData.ignorar) {
			sql.append(virgula+" cha_PF = '"+VOcha.getCha_PF()+"' ");
			virgula = ",";
		}
		
		sql.append(" WHERE cha_Cod_Cha = '"+VOcha.getCha_Cod_Cha()+"' ");
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0, sql.length()));
		//System.out.println(pstmt);
		int registrosAfetados = pstmt.executeUpdate();
		con.close();
		return registrosAfetados;
	}	
	
	// Deletar Chamada
	public int deletar(ChamadaVO VOcha) throws ClassNotFoundException, SQLException {		
		Connection con = Connect.getConnection(userVO);
		PreparedStatement pstmt = con.prepareStatement(
		"DELETE FROM chamada WHERE cha_Cod_Cha = ?");
		pstmt.setString(1, VOcha.getCha_Cod_Cha());
		int registrosAfetados = pstmt.executeUpdate();
		con.close();
		return registrosAfetados;
	}

	// Deletar Chamada
	public int deletarGrupo(ChamadaVO VOcha) throws ClassNotFoundException, SQLException {		
		Connection con = Connect.getConnection(userVO);
		PreparedStatement pstmt = con.prepareStatement(
		"DELETE FROM chamada WHERE cha_Cod_Cha IN ("+VOcha.getCha_Cod_Cha()+") ");
		System.out.println(pstmt);
		int registrosAfetados = pstmt.executeUpdate();
		con.close();
		return registrosAfetados;
	}

	//Pesquisar Chamada
	public ChamadaVO pesquisar(ChamadaVO VOcha) throws ClassNotFoundException, SQLException {				
		Connection con = Connect.getConnection(userVO);
		StringBuffer sql = new StringBuffer("SELECT * FROM chamada  Where cha_Cod_Cha <> '' ");
		
		if (!VOcha.getCha_Cod_Cha().equals(FString.ignorar)) {
			sql.append(" AND UPPER(cha_Cod_Cha)= '"+VOcha.getCha_Cod_Cha()+"' ");			
		}		
		if (!VOcha.getAlu_Cod_Alu().equals(FString.ignorar)) {
			sql.append(" AND UPPER(alu_Cod_Alu) LIKE UPPER('"+VOcha.getAlu_Cod_Alu()+"' "); 			
		}
		if (!VOcha.getIgr_Cod_Igreja().equals(FString.ignorar)) {
			sql.append(" AND UPPER(igr_Cod_Igreja) = '"+VOcha.getIgr_Cod_Igreja()+"' ");			
		}
		
		if (!VOcha.getTur_Cod_Tur().equals(FString.ignorar)) {
			sql.append(" AND tur_Cod_Tur = '"+VOcha.getTur_Cod_Tur()+"' ");
		}
		if (VOcha.getCha_DT_Cha()  != FData.ignorarD) {
			sql.append(" AND cha_DT_Cha = '"+(VOcha.getCha_DT_Cha()== FData.ignorarD || VOcha.getCha_DT_Cha() == null? FData.dataDefaultSQL(): VOcha.getCha_DT_Cha_SQL())+"' ");
		}
		
		if (VOcha.getCha_PF() != FData.ignorar) {
			sql.append(" AND cha_PF = '"+VOcha.getCha_PF()+"' ");
		}

		System.out.println("Turma..:"+VOcha.getTur_Cod_Tur());
		System.out.println("Classe.:"+VOcha.getCla_Cod_Cla());
		System.out.println("Presen.:"+VOcha.getCha_PF());
		System.out.println("Igreja.:"+VOcha.getIgr_Cod_Igreja());


		
		System.out.println(sql);
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));		
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {

			VOcha = new ChamadaVO();
			VOcha.setCha_Cod_Cha   ( rs.getString("cha_Cod_Cha"));
			VOcha.setAlu_Cod_Alu   ( rs.getString("alu_Cod_Alu"));
			VOcha.setIgr_Cod_Igreja( rs.getString("igr_Cod_Igreja"));
			VOcha.setTur_Cod_Tur   ( rs.getString("tur_Cod_Tur"));
			VOcha.setCla_Cod_Cla   ( rs.getString("cla_Cod_Cla"));
			VOcha.setCha_DT_Cha    ( rs.getDate  ("cha_DT_Cha"));
			VOcha.setCha_PF        ( rs.getString("cha_PF"));

		}else {
			VOcha = null;
		}
		pstmt.close();
		rs.close();
		con.close();
		return VOcha;
	}

	public ArrayList<ChamadaVO> geral(ChamadaVO VOcha) throws SQLException, ClassNotFoundException {		
		ArrayList<ChamadaVO> lista = null;
		Connection con = Connect.getConnection(userVO);
		StringBuffer sql = new StringBuffer("SELECT cha.*, c.cla_Cod_Cla, t.tur_Cod_Tur, a.alu_Cod_Alu "+ 
		" FROM chamada cha , aluno a, turma t LEFT OUTER JOIN classe c ON cha.alu_Cod_Alu = a.alu_Cod_Alu "+ 
		" Where cha.cha_Cod_Cha <> '' ");
		
		if (!VOcha.getCha_Cod_Cha().equals(FString.ignorar) && !VOcha.getCha_Cod_Cha().isEmpty()) {
			sql.append(" AND UPPER(cha.cha_Cod_Cha) = ('"+VOcha.getCha_Cod_Cha()+"')");
		}
		if (!VOcha.getAlu_Cod_Alu().equals(FString.ignorar) && !VOcha.getAlu_Cod_Alu().isEmpty()) {
			sql.append(" AND UPPER(cha.alu_Cod_Alu) = ('"+VOcha.getAlu_Cod_Alu()+"')");
		}
		if (!VOcha.getIgr_Cod_Igreja().equals(FString.ignorar) && !VOcha.getIgr_Cod_Igreja().isEmpty()) {
			sql.append(" AND UPPER(cha.igr_Cod_Igreja) LIKE UPPER('"+VOcha.getIgr_Cod_Igreja()+"%"+"')");
		}
		if (!VOcha.getTur_Cod_Tur().equals(FString.ignorar) && !VOcha.getTur_Cod_Tur().isEmpty()) {
			sql.append(" AND cha.tur_Cod_Tur = '"+VOcha.getTur_Cod_Tur()+"' ");
		}

		if (VOcha.getCha_DT_Cha()  != FData.ignorarD) {
			sql.append(" AND cha.cha_DT_Cha = '"+ VOcha.getCha_DT_Cha_SQL()+"' ");
		}

		if (VOcha.getCha_PF() != FString.ignorar) {
			sql.append(" AND cha.cha_PF = '"+ VOcha.getCha_PF()+"' ");
		}
		
		if (!VOcha.getOrdem().equals(FString.ignorar)) {
			sql.append(" ORDER BY "+VOcha.getOrdem());
		}
		if (!VOcha.getLimit().equals(FString.ignorar)) {
			sql.append(" LIMIT "+VOcha.getLimit());
		}
		//System.out.println(sql.toString());
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));		
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			lista = new ArrayList<ChamadaVO>();
			do {
				ChamadaVO cha = new ChamadaVO();
				cha.setCha_Cod_Cha   ( rs.getString("cha_Cod_Cha"));
				cha.setAlu_Cod_Alu   ( rs.getString("alu_Cod_Alu"));
				cha.setIgr_Cod_Igreja( rs.getString("igr_Cod_Igreja"));
				cha.setTur_Cod_Tur   ( rs.getString("tur_Cod_Tur"));
				cha.setCla_Cod_Cla   ( rs.getString("cla_Cod_Cla"));
				cha.setCha_DT_Cha    ( rs.getDate  ("cha_DT_Cha"));
				cha.setCha_PF        ( rs.getString("cha_PF"));
				lista.add(cha);
			}
			while (rs.next());			
		}
		pstmt.close();
		rs.close();
		con.close();
		return lista;
	}

	public ArrayList<ChamadaVO> geralConsolidado(ChamadaVO VOcha) throws SQLException, ClassNotFoundException {		
		ArrayList<ChamadaVO> lista = null;
		Connection con = Connect.getConnection(userVO);
		StringBuffer sql = new StringBuffer("SELECT cha.alu_Cod_Alu, cha.igr_Cod_Igreja, cha.tur_Cod_Tur, SUM(cha_PF) as 'Tot_Presenca', "+  
                                            "MAX(cha_PF) as 'Max_PF', t.tur_Cod_Tur, c.cla_Cod_Cla  "+
				                            "FROM chamada cha, turma t, classe c, aluno a	"+			
				                            "Where cha.alu_Cod_Alu = a.alu_Cod_Alu and cha.cha_Cod_Cha <> ''  "+
				                            "GROUP by cha.alu_Cod_Alu,cha.igr_Cod_Igreja,cha.tur_Cod_Tur ");		
		
		if (!VOcha.getCha_Cod_Cha().equals(FString.ignorar) && !VOcha.getCha_Cod_Cha().isEmpty()) {
			sql.append(" AND UPPER(cha.cha_Cod_Cha) = ('"+VOcha.getCha_Cod_Cha()+"')");
		}
		if (!VOcha.getAlu_Cod_Alu().equals(FString.ignorar) && !VOcha.getAlu_Cod_Alu().isEmpty()) {
			sql.append(" AND UPPER(cha.alu_Cod_Alu) LIKE UPPER('"+VOcha.getAlu_Cod_Alu()+"')");
		}
		if (!VOcha.getIgr_Cod_Igreja().equals(FString.ignorar) && !VOcha.getIgr_Cod_Igreja().isEmpty()) {
			sql.append(" AND UPPER(cha.igr_Cod_Igreja) LIKE UPPER('"+VOcha.getIgr_Cod_Igreja()+"')");
		}
		if (!VOcha.getTur_Cod_Tur().equals(FString.ignorar) && !VOcha.getTur_Cod_Tur().isEmpty()) {
			sql.append(" AND cha.tur_Cod_Tur = '"+VOcha.getTur_Cod_Tur()+"' ");
		}
		if (VOcha.getCha_DT_Cha()  != FData.ignorarD) {
			sql.append(" AND cha.cha_DT_Cha = '"+ VOcha.getCha_DT_Cha_SQL()+"' ");
		}
		
		if (VOcha.getCha_PF() != FString.ignorar) {
			sql.append(" AND a.cha_PF = '"+ VOcha.getCha_PF()+"' ");
		}
		
		sql.append(" GROUP BY a.alu_Cod_Alu, a.igr_Cod_Igreja, a.tur_Cod_Tur ");
		
		if (!VOcha.getOrdem().equals(FString.ignorar)) {
			sql.append(" ORDER BY "+VOcha.getOrdem());
		}
		if (!VOcha.getLimit().equals(FString.ignorar)) {
			sql.append(" LIMIT "+VOcha.getLimit());
		}
		
		System.out.println(sql.toString());
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));		
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			lista = new ArrayList<ChamadaVO>();
			do {
				ChamadaVO cha = new ChamadaVO();
				cha.setAlu_Cod_Alu   ( rs.getString("alu_Cod_Alu"));
				cha.setIgr_Cod_Igreja( rs.getString("igr_Cod_Igreja"));
				cha.setTur_Cod_Tur   ( rs.getString("tur_Cod_Tur"));
				cha.setCla_Cod_Cla   ( rs.getString("cla_Cod_Cla"));
				cha.setCha_DT_Cha    ( rs.getDate  ("cha_DT_Cha"));
				cha.setCha_PF        ( rs.getString("cha_PF"));

				lista.add(cha);
			}
			while (rs.next());			
		}
		pstmt.close();
		rs.close();
		con.close();
		return lista;
	}

	public ResultSet graficoEvolucao(ChamadaVO VOcha, Connection con) throws SQLException, ClassNotFoundException {		
		StringBuffer sql = new StringBuffer("SELECT cha.igr_Cod_Igreja, cha.cla_Cod_Cla ,cha.tur_Cod_Tur, concat(MONTH(cha_DT_Cha),'/', YEAR(cha_DT_Cha)) AS 'mesano' "+//, SUM(cla_Cod_Cla) as 'pontos' " + 
				" FROM chamada cha " +				
				" LEFT OUTER JOIN aluno a ON a.alu_Cod_Alu = cha.alu_Cod_Alu " +
				" Where a.cha_Cod_Cha <> ''  ");
		if (!VOcha.getCha_Cod_Cha().equals(FString.ignorar) && !VOcha.getCha_Cod_Cha().isEmpty()) {
			sql.append(" AND UPPER(a.cha_Cod_Cha) = ('"+VOcha.getCha_Cod_Cha()+"')");
		}
		
		if (!VOcha.getAlu_Cod_Alu().equals(FString.ignorar) && !VOcha.getAlu_Cod_Alu().isEmpty()) {
			sql.append(" AND UPPER(a.alu_Cod_Alu) LIKE UPPER('"+VOcha.getAlu_Cod_Alu()+"%"+"')");
		}
		
		if (!VOcha.getIgr_Cod_Igreja().equals(FString.ignorar) && !VOcha.getIgr_Cod_Igreja().isEmpty()) {
			sql.append(" AND UPPER(a.igr_Cod_Igreja) LIKE UPPER('"+VOcha.getIgr_Cod_Igreja()+"%"+"')");
		}
		if (!VOcha.getTur_Cod_Tur().equals(FString.ignorar) && !VOcha.getTur_Cod_Tur().isEmpty()) {
			sql.append(" AND a.tur_Cod_Tur = '"+VOcha.getTur_Cod_Tur()+"' ");
		}
		
		if (VOcha.getCha_DT_Cha()  != FData.ignorarD && !VOcha.getCha_DT_Cha_SQL().equals(FData.dataDefaultSQL())) {
			sql.append(" AND a.cha_DT_Cha = '"+ VOcha.getCha_DT_Cha_SQL()+"' ");
		}
		
		if (VOcha.getCha_PF() .equals(FString.ignorar) && !VOcha.getCha_PF().isEmpty()) {
			sql.append(" AND a.cha_PF = '"+ VOcha.getCha_PF()+"' ");
		}
		
		//sql.append(" GROUP BY a.igr_Cod_Igreja, a.tur_Cod_Tur, MONTH(cha_DT_Cha), YEAR(cha_DT_Cha) ");
		
		if (!VOcha.getOrdem().equals(FString.ignorar) && !VOcha.getOrdem().isEmpty()) {
			sql.append(" ORDER BY "+VOcha.getOrdem());
		}
		if (!VOcha.getLimit().equals(FString.ignorar)) {
			sql.append(" LIMIT "+VOcha.getLimit());
		}
		System.out.println(sql.toString());
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));		
		ResultSet rs = pstmt.executeQuery();
		return rs;
	}

	public ResultSet listagem(ChamadaVO VOcha, Connection con) throws SQLException, ClassNotFoundException {		

		StringBuffer sql = new StringBuffer("SELECT chamada.`cha_Cod_Cha` AS chamada_cha_Cod_Cha, "+
			     " chamada.`alu_Cod_Alu` AS chamada_alu_Cod_Alu, "+
			     " chamada.`igr_Cod_Igreja` AS chamada_igr_Cod_Igreja, "+
			     " chamada.`cla_Cod_Cla` AS chamada_cla_Cod_Cla, "+
			     " chamada.`tur_Cod_Tur` AS chamada_tur_Cod_Tur, "+
			     " chamada.`cha_PF` AS chamada_cha_PF, "+
			     " chamada.`cha_DT_Cha` AS chamada_cha_DT_Cha, "+
			     " igrejas.`igr_Descricao` AS igrejas_igr_Descricao, "+
			     " classe.`cla_Descricao` AS classe_cla_Descricao, "+
			     " turma.`tur_Descricao` AS turma_tur_Descricao, "+
			     " aluno.`alu_Descricao` AS aluno_alu_Descricao "+
			     " FROM `igrejas` igrejas INNER JOIN `chamada` chamada ON igrejas.`igr_Cod_Igreja` = chamada.`igr_Cod_Igreja` "+
			     " INNER JOIN `classe` classe ON chamada.`cla_Cod_Cla` = classe.`cla_Cod_Cla` "+
			     " INNER JOIN `turma` turma ON chamada.`tur_Cod_Tur` = turma.`tur_Cod_Tur` "+
			     " INNER JOIN `aluno` aluno ON chamada.`alu_Cod_Alu` = aluno.`alu_Cod_Alu`	"+
            	 " Where chamada.`cha_Cod_Cha` <> '' ");
				
				
		if (!VOcha.getCha_Cod_Cha().equals(FString.ignorar) && !VOcha.getCha_Cod_Cha().isEmpty()) {
			sql.append(" AND UPPER(chamada.`cha_Cod_Cha`) = '"+VOcha.getCha_Cod_Cha()+"' ");			
		}		
		if (!VOcha.getAlu_Cod_Alu().equals(FString.ignorar) && !VOcha.getAlu_Cod_Alu().isEmpty()) {
			sql.append(" AND UPPER(chamada.`alu_Cod_Alu`) = '"+VOcha.getAlu_Cod_Alu()+"' "); 			
		}

		if (!VOcha.getIgr_Cod_Igreja().equals(FString.ignorar) && !VOcha.getIgr_Cod_Igreja().isEmpty()) {
			sql.append(" AND UPPER(chamada.`igr_Cod_Igreja`) = '"+VOcha.getIgr_Cod_Igreja()+"' ");
		}

		if (!VOcha.getCla_Cod_Cla().equals(FString.ignorar) && !VOcha.getCla_Cod_Cla().isEmpty()){
			sql.append(" AND UPPER(chamada.`cla_Cod_Cla`) = '"+VOcha.getCla_Cod_Cla()+"' ");
		}
		
		if (!VOcha.getTur_Cod_Tur().equals(FString.ignorar) && !VOcha.getTur_Cod_Tur().isEmpty()){
			sql.append(" AND UPPER(chamada.`tur_Cod_Tur`) = '"+VOcha.getTur_Cod_Tur()+"' ");
		}

		if (VOcha.getCha_DT_Cha()  != FData.ignorarD && !VOcha.getCha_DT_Cha_String().isEmpty() && !VOcha.getCha_DT_Cha_String().equals(FData.dataDefault())) {
			sql.append(" AND UPPER(chamada.`cha_DT_Cha`) = '"+VOcha.getCha_DT_Cha_SQL()+"' ");
		}
		
		if (VOcha.getCha_PF() != FData.ignorar && !VOcha.getCha_PF().isEmpty())  {
			sql.append(" AND UPPER(chamada.`cha_PF`) = '"+VOcha.getCha_PF()+"' ");
		}

		
		if (!VOcha.getOrdem().equals(FString.ignorar) && !VOcha.getOrdem().isEmpty()) {
			sql.append(" ORDER BY "+VOcha.getOrdem());
		}
		if (!VOcha.getLimit().equals(FString.ignorar)) {
			sql.append(" LIMIT "+VOcha.getLimit());
		}
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));
		//System.out.println(pstmt);
		ResultSet rs = pstmt.executeQuery();
		return rs;
	}
	
}